﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace VideoArchive
{
    public class ConfigDataBase
    {
        private string dbcon = null;
        private SQLiteConnection m_cnADONetConnection;
        private SQLiteDataAdapter Ad_ms_Table;
        private SQLiteCommandBuilder CmBl_ms_Table;

        public ConfigDataBase()
        {
            dbcon = "Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "data;Version=3;Read Only=False;";
      
        }

        public bool checkConnection()
        {
            bool chk = true;
            try
            {
                m_cnADONetConnection = new SQLiteConnection(dbcon);
                m_cnADONetConnection.Open();
                m_cnADONetConnection.Close();
            }
            catch (SQLiteException ex) { chk = false; }
            return chk;
        }

        public DataTable getTable(string sql)
        {
            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new SQLiteConnection(dbcon);
            Ad_ms_Table = new SQLiteDataAdapter();
            CmBl_ms_Table = new SQLiteCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SQLiteCommand(sql, m_cnADONetConnection);
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

        public void sqlExecute(string sql)
        {
            m_cnADONetConnection = new SQLiteConnection(dbcon);
            SQLiteCommand cmd = new SQLiteCommand(sql, m_cnADONetConnection);
            try
            {
                m_cnADONetConnection.Open();
                cmd.ExecuteNonQuery();
                m_cnADONetConnection.Close();
            }
            catch (SQLiteException e)
            {
                Console.WriteLine("Error: {0}", e.Message);
            }

        }

        public DataTable procedure(string name)
        {

            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new SQLiteConnection(dbcon);
            Ad_ms_Table = new SQLiteDataAdapter();
            CmBl_ms_Table = new SQLiteCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SQLiteCommand(name, m_cnADONetConnection);
            Ad_ms_Table.SelectCommand.CommandType = CommandType.StoredProcedure;
            Ad_ms_Table.SelectCommand.Parameters.Clear();
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

       
    }
}
